#!/usr/bin/perl -w

BEGIN {
	push(@INC,"/home/modules/");
};
use strict;
use utf8;
use Spreadsheet::ParseExcel;
use Spreadsheet::XLSX;
use Text::Iconv;
use Encode;
use JSON;
use Cwd 'abs_path';

abs_path($0)=~/^(.*[\\\/])/;
my $logpath="$1/logs/xlsx_to_json_table.log";
sub mywarn {
	open OUT,">>$logpath" or print STDERR "Could not write to $logpath\n";
	foreach (@_) {
		print OUT $_;
		print OUT "\n" unless $_=~/\n$/;
#		print $_;
#		print "\n" unless $_=~/\n$/;
	}
	close OUT;
}

sub mydie {
	mywarn(@_);
	exit(1);
}

{
	$SIG{__WARN__}=\&mywarn;
	$SIG{__DIE__}=\&mydie;
	$SIG{INT}=\&mydie;

	warn "\n\n".localtime()."\n";
	warn $0." ".join(" ",@ARGV)."\n";
	die "Wrong parameters\n" if  @ARGV!=1;
	my ($xlsfile)=@ARGV;
	
	my $parser;
	my $workbook;
	my $xlsx;
	if ($xlsfile=~/\.xls$/) {
		$parser=Spreadsheet::ParseExcel->new();
		$workbook=$parser->parse($xlsfile);
		die $parser->error(), ".\n" unless defined $workbook;
	} elsif ($xlsfile=~/\.xlsx$/) {
		$xlsx=1;
		my $converter;# = Text::Iconv -> new ("utf-8", "windows-1251");
		$workbook=Spreadsheet::XLSX -> new ($xlsfile, $converter);
	} else {
		die "Unknown file format - $xlsfile";
	}
	my $jsonfile=$xlsfile;
	$jsonfile=~s/.xlsx?$/.json/i;
	
	my $data=[];
	my @worksheets=$workbook->worksheets();
	warn "Number of worksheets = ".($#worksheets+1)."\n";
	my $worksheet=$worksheets[0];	
		my ( $row_min, $row_max ) = $worksheet->row_range();
		my ( $col_min, $col_max ) = $worksheet->col_range();

		for my $row ( $row_min .. $row_max ) {
			for my $col ( $col_min .. $col_max ) {
				my $val;
				if ($xlsx==0) {
					my $cell = $worksheet->get_cell( $row, $col );
					next unless $cell;
					$val=$cell->value();
				} else {
					my $cell=$worksheet -> {Cells} [$row] [$col];
					next unless $cell;
					$val=$cell -> {Val};
				}
				$val=~s/\xa0/ /g unless $xlsx;
				$val=~s/^\s+//;
				$val=~s/\s+$//;

				# fix xml
				$val =~ s/&amp;/&/g;
				$val =~ s/&gt;/>/g;
				$val =~ s/&lt;/</g;
				$val =~ s/&quot;/"/g;
				$val =~ s/&apos;/'/g;
				$val =~ s/&#xA;/\n/g;
				$val =~ s/&#xa;/\n/g;
				$val =~ s/&#xD;/\r/g;
				$val =~ s/&#xd;/\r/g;
				$val =~ s/&#x9;/\t/g;

				Encode::_utf8_on($val);
				$data->[$row]->[$col]=$val;
			}
		}
		# only first page
	
	open OUT,">$jsonfile" or die;
	print OUT encode_json($data);
	close OUT;
}

